/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package model;

import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.derby.drda.NetworkServerControl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author leadiv
 */
public class ProfileSQL {
    private Connection conn;
    
    public ProfileSQL() 
    {
        String port = System.getProperty("derby.drda.portNumber");
        String nsURL="jdbc:derby://localhost:" + port + "/deliciousBlogger";
        java.util.Properties props = new java.util.Properties();
        props.setProperty("user","dbuser");
        props.setProperty("password","f6i53t");
        
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            conn = DriverManager.getConnection(nsURL, props);

        } catch (Exception ex) {
            Logger.getLogger(ProfileSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void printUsers()
    {
        String sql = "select USERNAME from \"Model\".USERS";

        try {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);

            while(rs.next()) {
                System.out.println("Name: " + rs.getString(1));
            }

            rs.close();
            s.close();
            
        } catch(Exception ex) {
            Logger.getLogger(ProfileSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public boolean verifyUser(String u, String p)
    {
        int count = 0;
        String sql = "select count(*) from \"Model\".USERS where USERNAME='<u>' and PASSWORD='<p>'";
        sql = sql.replace("<u>", u);
        sql = sql.replace("<p>", p);

        try {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);

            rs.next();
            count = Integer.valueOf(rs.getString(1));

            System.out.println("COUNT: " + count);
            
            rs.close();
            s.close();
        } catch(Exception ex) {
            Logger.getLogger(ProfileSQL.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("SQL: " + sql);
        }

        return (count == 1) ? true : false;
    }

    public int getUserID(String u)
    {
        return 1226;
    }

    public void shutdown()
    {
        NetworkServerControl server;

        try {
            server = new NetworkServerControl();

            conn.close();
            server.shutdown();
        } catch(Exception ex) {
            Logger.getLogger(ProfileSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void serverInfo()
    {
        NetworkServerControl server;

        try {
            server = new NetworkServerControl();

            server.getSysinfo();
        } catch(Exception ex) {
            Logger.getLogger(ProfileSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}
